﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using MySql.Data.MySqlClient;
using System.Data;
using FrameWork.Lib;
using FrameWork.Lib.Diagnostics;
namespace REVISIONMAX30
{
    public static class MySQLDBHandler
    {
        public static Result ExecuteDumpDBWithMySQLBatch(string pUser, string pPassword, string pDataBase, string pDBResultFile, string pMySQLBinPath)
        {
            var tResult = new Result();
            var tWaitForExit = true;

            var tBatchTempFile = AppDomain.CurrentDomain.BaseDirectory + Guid.NewGuid().ToString() + "_run.bat";

            var tCommandBuilder = new StringBuilder();

            tCommandBuilder.Append(string.Format("\"{0}{1}\"", pMySQLBinPath, "mysqldump.exe"));
            tCommandBuilder.AppendLine(string.Format(" \"{2}\" --user={0} --password={1} --result-file=\"{3}\"", new object[] { pUser, pPassword, pDataBase, pDBResultFile }));

            File.WriteAllText(tBatchTempFile, tCommandBuilder.ToString());

            tResult.AddResult(Functions.ExecuteBatch(tBatchTempFile, tWaitForExit));

            if (tWaitForExit)
            {
                File.Delete(tBatchTempFile);
            }

            return tResult;
        }

        public static Result ExecuteSQLFileWithMySQLBatch(string pUser, string pPassword, string pDataBase, string pSQLFileName)
        {
            var tResult = new Result();
            var tWaitForExit = true;

            var tBatchTempFile = AppDomain.CurrentDomain.BaseDirectory + Guid.NewGuid().ToString() + "_run.bat";

            var tCommandBuilder = new StringBuilder();

            tCommandBuilder.Append(string.Format("\"{0}{1}\"", Common.DefaultMySQLBatchPath, "mysql.exe"));
            tCommandBuilder.Append(string.Format(" --user={0} --password={1} --database={2} --show-warnings <\"{3}\"", new object[] { pUser, pPassword, pDataBase, pSQLFileName }));

            File.WriteAllText(tBatchTempFile, tCommandBuilder.ToString());

            tResult.AddResult(Functions.ExecuteBatch(tBatchTempFile, tWaitForExit));

            if (tWaitForExit)
            {
                File.Delete(tBatchTempFile);
            }

            return tResult;
        }

        public static Result ExecuteSQLFileViaMYSQL(string pSQLFileName, string pConnectionString)
        {
            var tResult = new Result();

            var tStringBuilder = new StringBuilder();

            using (var tStreamReader = new StreamReader(pSQLFileName))
            {
                using (var tConnection = new MySqlConnection(pConnectionString))
                {
                    tConnection.Open();

                    using (var tTransaction = tConnection.BeginTransaction())
                    {
                        int tLine = 0;

                        while (!tStreamReader.EndOfStream)
                        {
                            using (var tCommand = tConnection.CreateCommand())
                            {
                                tLine++;

                                string tCommandText = tStreamReader.ReadLine();

                                tCommand.CommandType = CommandType.Text;

                                tCommand.CommandText = tCommandText;

                                try
                                {
                                    if (tCommand.ExecuteNonQuery() <= 0)
                                    {
                                        tResult.AddResult(string.Format("Warning: {0}, Line: {1}, CommandText: {2}", "[record not inserted]", tLine, tCommandText), ResultSeverityType.Warning);
                                    }
                                }
                                catch (Exception exc)
                                {
                                    tResult.AddResult(string.Format("Error: {0}, Line: {1}, CommandText: {2}", exc.Message, tLine, tCommandText), ResultSeverityType.Error);
                                }
                            }
                        }
                        tTransaction.Commit();
                    }
                    tConnection.Close();
                }

                tStreamReader.Close();
            }

            return tResult;
        }

        public static Result TestConnection(string pConnectionString)
        {
            var tResult = new Result();

            //MySqlConnection tConnection = null;
            //try
            //{
            //    tConnection = new MySqlConnection(pConnectionString);
            //    tConnection.Open();
            //    tConnection.Close();

            //    tResult.AddResult("Test Sucessfull.", ResultSeverityType.Info);
            //}
            //catch (Exception exc)
            //{
            //    tResult.AddResult(exc);
            //}
            //finally
            //{
            //    tConnection.TryClose();
            //}
            return tResult;
        }



        public static DataSet ExecuteGetDataSet(string pCommandText, string pConnectionString)
        {
            var tDataSet = new DataSet();
            using (var tConnection = new MySqlConnection(pConnectionString))
            {
                using (var tDataAdapter = new MySqlDataAdapter(pCommandText, tConnection))
                {
                    tDataAdapter.UpdateBatchSize = 500;
                    tDataAdapter.ContinueUpdateOnError = false;
                    tConnection.Open();
                    tDataAdapter.Fill(tDataSet);
                    tConnection.Close();
                }
            }
            return tDataSet;
        }

        public static DataTable ExecuteGetDataTable(string pCommandText, string pConnectionString)
        {
            var tDataTable = new DataTable();
            using (var tConnection = new MySqlConnection(pConnectionString))
            {
                using (var tDataAdapter = new MySqlDataAdapter(pCommandText, tConnection))
                {
                    tDataAdapter.ContinueUpdateOnError = false;
                    tConnection.Open();
                    tDataAdapter.Fill(tDataTable);
                    tConnection.Close();
                }
            }
            return tDataTable;
        }

        public static int ExecuteNonQuery(string pCommandText, string pConnectionString)
        {
            int num = 0;
            using (var tConnection = new MySqlConnection(pConnectionString))
            {
                using (var tDataAdapter = new MySqlDataAdapter())
                {
                    tDataAdapter.UpdateBatchSize = 500;
                    tDataAdapter.ContinueUpdateOnError = false;
                    tDataAdapter.SelectCommand = new MySqlCommand(pCommandText, tConnection);
                    tConnection.Open();
                    num = tDataAdapter.SelectCommand.ExecuteNonQuery();
                    tConnection.Close();
                }
            }
            return num;
        }

        public static string GetConnectionString(string pDataBase)
        {
            var tConnectionStringBuilder = new MySqlConnectionStringBuilder();

            tConnectionStringBuilder.AllowBatch = true;
            tConnectionStringBuilder.Database = pDataBase;
            tConnectionStringBuilder.UserID = "root";
            tConnectionStringBuilder.Password = "root";
            tConnectionStringBuilder.Server = "mikedb01";
            tConnectionStringBuilder.Port = 0xcea;
            tConnectionStringBuilder.PersistSecurityInfo = false;

            return tConnectionStringBuilder.ConnectionString;
        }
 
        public static void FillListWithDatabaseNames(System.Collections.IList pList, string pConnectionString)
        {
            var tDataTable = ExecuteGetDataTable("show databases;", pConnectionString);
            pList.Clear();
            var tRows = tDataTable.Select("(not database = 'mysql' AND not database = 'information_schema')", tDataTable.Columns[0].ColumnName + " asc");
            foreach (var tRow in tRows)
            {
                pList.Add(tRow[0].StringValueOrEmpty());
            }
  
        }

        public static DataTable GetTableColumns(string pTableName, string pConnectionString)
        {
            return ExecuteGetDataTable("DESCRIBE `" + pTableName + "`;", pConnectionString);
        }

        public static List<string> GetTables(string pConnectionString)
        {
            var tDataTable = ExecuteGetDataTable("show tables;", pConnectionString);
            var tList = new List<string>();
            var tRows = tDataTable.Select("", tDataTable.Columns[0].ColumnName + " asc");
            foreach (var tRow in tRows)
            {
                tList.Add(tRow[0].StringValueOrEmpty());
            }
            return tList;
        }

  
    }
}
